package util;

import bean.TourShift;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TourShiftDao {
    //增加
    public void addTourShift(TourShift ts) throws SQLException {
        System.out.println(ts);
        Connection conn = DbUtil.getConnection();
        //sql
        String sql="insert into TourShift"+"(shiftNo,departureDate,tourismStandard,quote,discountRate,routeNo)"+"values("+"'"+ts.getShiftNo()+"'"+","+"'"+ts.getDepartureDate()+"'"+","+"'"+ts.getTourismStandard()+"'"+","+"'"+ts.getQuote()+"'"+","+"'"+ts.getDiscountRate()+"'"+","+"'"+ts.getRouteNo()+"'"+")";
        System.out.println(sql);
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
        //更新编号
        System.out.println(ts.getShiftNo());
        ptmt = conn.prepareStatement("UPDATE UPDATNO set shiftNo='"+ts.getShiftNo()+"' WHERE No='1'");//更新
        ptmt.execute();
        System.out.println(ts.getShiftNo());
        System.out.println("add tourShift success!");
    }

    //删除(shiftNo)
    public void delTourShift(String shitfNo) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "delete from TourShift where shiftNo=?";
        //预编译；预编译SQL,减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //传参
        ptmt.setString(1,shitfNo);
        ptmt.execute();
    }

    //更新(routeNo,shiftNo不允许更改)
    public void updateTourShift(String shiftNo,String departureDate,String tourismStandard,int quote,float discountRate) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "UPDATE TourShift set departureDate='"+departureDate+"',tourismStandard='"+tourismStandard+"',quote='"+quote+"',discountRate='"+discountRate+"' where shiftNo='"+shiftNo+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.execute();
    }

    //查(shiftNo)
    public TourShift get(String shiftNo) throws SQLException {
        TourShift ts = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  TourShift where shiftNo='"+shiftNo+"'";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            ts = new TourShift();
            ts.setShiftNo(rs.getString("shiftNo"));
            ts.setDepartureDate(rs.getString("departureDate"));
            ts.setTourismStandard(rs.getString("tourismStandard"));
            ts.setQuote(rs.getInt("quote"));
            ts.setDiscountRate(rs.getFloat("discountRate"));
            ts.setRouteNo(rs.getString("routeNo"));
        }
        return ts;
    }

    //根据routeNo查找
    public List<TourShift> fromRoute(String routeNo) throws SQLException {
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  TourShift where routeNo='"+routeNo+"'";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        List<TourShift> TSList = new ArrayList<TourShift>();
        TourShift ts = null;
        while (rs.next()){
            ts = new TourShift();
            ts.setShiftNo(rs.getString("shiftNo"));
            ts.setDepartureDate(rs.getString("departureDate"));
            ts.setTourismStandard(rs.getString("tourismStandard"));
            ts.setQuote(rs.getInt("quote"));
            ts.setDiscountRate(rs.getFloat("discountRate"));
            ts.setRouteNo(rs.getString("routeNo"));

            TSList.add(ts);
        }
        System.out.println(TSList.size());
        for (TourShift TS : TSList){
            System.out.println(TS.getRouteNo()+"-"+TS.getDiscountRate());
        }
        
        return TSList;
    }

    //搜索框
    public List<TourShift> search(String find) throws SQLException {
        Connection conn = DbUtil.getConnection();
        List<TourShift> TSList = new ArrayList<TourShift>();
        Statement stmt = conn.createStatement();
        String[] sql = new String[5];
        sql[0] = "select * from  TourShift where shiftNo='"+find+"'";
        sql[1] = "select * from  TourShift where departureDate='"+find+"'";
        sql[2] = "select * from  TourShift where tourismStandard='"+find+"'";
        sql[3] = "select * from  TourShift where quote='"+find+"'";
        sql[4] = "select * from  TourShift where discountRate='"+find+"'";
        int flag = DbUtil.isNum(find);  //0字符串，1小数，2整数
        System.out.println(find);

        for (int i=0;i<5;i++){
            if (flag==1 && i==3){
                System.out.println(flag);
                continue;
            } else if (flag==0){
                if (i==3 || i==4)
                    continue;
            }else { }
            ResultSet rs = stmt.executeQuery(sql[i]);
            while (rs.next()){
                TourShift ts = new TourShift();
                ts.setShiftNo(rs.getString("shiftNo"));
                ts.setDepartureDate(rs.getString("departureDate"));
                ts.setTourismStandard(rs.getString("tourismStandard"));
                ts.setQuote(rs.getInt("quote"));
                ts.setDiscountRate(rs.getFloat("discountRate"));
                TSList.add(ts);
            }
        }
        return TSList;
    }

    public List<TourShift> query() throws SQLException {
        Connection conn = DbUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM TourShift");

        List<TourShift> TSList = new ArrayList<TourShift>();
        TourShift ts = null;
        while (rs.next()){
            ts = new TourShift();
            ts.setShiftNo(rs.getString("shiftNo"));
            ts.setDepartureDate(rs.getString("departureDate"));
            ts.setTourismStandard(rs.getString("tourismStandard"));
            ts.setQuote(rs.getInt("quote"));
            ts.setDiscountRate(rs.getFloat("discountRate"));
            ts.setRouteNo(rs.getString("routeNo"));

            TSList.add(ts);
        }

        return TSList;
    }
    public String updateShiftNo() throws SQLException {
        //获取已存在的最大（新）的shiftNo
        String shiftNo = null;
        Connection conn = DbUtil.getConnection();
        String sql = "select * from  UPDATNO";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ResultSet rs = ptmt.executeQuery();
        while (rs.next()){
            shiftNo = rs.getString("shiftNo");
        }
        char[] one = shiftNo.toCharArray();
        char first = one[0];
        String two = shiftNo.substring(1);
        int num = Integer.parseInt(two);
        num = num+1;
        System.out.println(one + Integer.toString(num) );
        shiftNo = first + Integer.toString(num);

        System.out.println(shiftNo);

        return shiftNo;
    }
}
